﻿using ShSoft.Common.PoweredByLee;
using SlamDunk.CMS.Model.DataModel;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace SlamDunk.CMS.DAL
{
    public class ArticleDal
    {
        #region # 静态构造器

        /// <summary>
        /// SqlHelper
        /// </summary>
        private static readonly SqlHelper _SqlHelper;

        /// <summary>
        /// 静态构造器
        /// </summary>
        static ArticleDal()
        {
            _SqlHelper = new SqlHelper(ConfigurationManager.ConnectionStrings["CMS"].ConnectionString);
        }

        #endregion

        #region 01.根据栏目Id获取文章列表
        /// <summary>
        /// 根据栏目Id获取文章列表
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章列表</returns>
        public List<Article> GetModelList(int channelId)
        {
            List<Article> list = new List<Article>();
            string sql = "SELECT * FROM Articles WHERE DelFlag = 0 AND ChannelId = @ChannelId";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId)))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 02.根据文章Id与栏目Id查询文章的索引
        /// <summary>
        /// 根据文章Id与栏目Id查询文章的索引
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章索引</returns>
        public int GetRowIndex(int id, int channelId)
        {
            string sql = "SELECT RowIndex FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.Id = @Id";
            DataTable dateTable = _SqlHelper.GetDataTable(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@Id", id));
            int rowIndex = Convert.ToInt32(dateTable.Rows[0][0]);
            return rowIndex;
        }
        #endregion

        #region 03.获取上一篇文章
        /// <summary>
        /// 获取上一篇文章
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章实体对象</returns>
        public Article GetPrevModel(int id, int channelId)
        {
            //获取文章索引
            int rowIndex = GetRowIndex(id, channelId);
            string sql = "SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.RowIndex < @RowIndex ORDER BY RowIndex DESC";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@RowIndex", rowIndex)))
            {
                if (reader.Read())
                {
                    return this.ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 04.获取下一篇文章
        /// <summary>
        /// 获取下一篇文章
        /// </summary>
        /// <param name="id">文章Id</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章实体对象</returns>
        public Article GetNextModel(int id, int channelId)
        {
            //获取文章索引
            int rowIndex = GetRowIndex(id, channelId);
            string sql = "SELECT TOP 1 * FROM (SELECT ROW_NUMBER() OVER (ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex, * FROM dbo.Articles  WHERE ChannelId = @ChannelId) AS T WHERE T.RowIndex > @RowIndex ORDER BY RowIndex ASC";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId), new SqlParameter("@RowIndex", rowIndex)))
            {
                if (reader.Read())
                {
                    return this.ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 04.根据栏目Id、开始时间、结束时间与标题关键字查询文章记录条数
        /// <summary>
        /// 根据栏目Id、开始时间、结束时间与标题关键字查询文章记录条数
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="title">标题关键字</param>
        /// <returns>符合条件的文章记录条数</returns>
        public int GetCount(int channelId, string startTime, string endTime, string title)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Articles WHERE ChannelId = @ChannelId";

            #region 非空校验
            if (!string.IsNullOrWhiteSpace(startTime))
            {
                sql = string.Format("{0} AND AddTime >= '{1}'", sql, startTime.FilterSql());
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                sql = string.Format("{0} AND AddTime <= '{1}'", sql, endTime.FilterSql());
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql = string.Format("{0} AND Title LIKE '%{1}%'", sql, title.FilterSql());
            }
            #endregion

            return (int)_SqlHelper.ExecuteScalar(sql, new SqlParameter("@ChannelId", channelId));
        }
        #endregion

        #region 05.根据栏目Id、开始时间、结束时间与标题关键字分页查询文章列表
        /// <summary>
        /// 根据栏目Id、开始时间、结束时间与标题关键字查询文章列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">结束行</param>
        /// <param name="channelId">栏目Id</param>
        /// <param name="startTime">开始时间</param>
        /// <param name="endTime">结束时间</param>
        /// <param name="title">标题关键字</param>
        /// <returns>文章列表</returns>
        public List<Article> GetModelList(int start, int end, int channelId, string startTime, string endTime, string title)
        {
            List<Article> list = new List<Article>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Articles WHERE ChannelId = @ChannelId";

            #region 非空校验
            if (!string.IsNullOrWhiteSpace(startTime))
            {
                sql = string.Format("{0} AND AddTime >= '{1}'", sql, startTime.FilterSql());
            }
            if (!string.IsNullOrWhiteSpace(endTime))
            {
                sql = string.Format("{0} AND AddTime <= '{1}'", sql, endTime.FilterSql());
            }
            if (!string.IsNullOrWhiteSpace(title))
            {
                sql = string.Format("{0} AND Title LIKE '%{1}%'", sql, title.FilterSql());
            }
            #endregion

            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@ChannelId", channelId),
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 06.根据栏目Id获取文章记录条数
        /// <summary>
        /// 根据栏目Id获取文章记录条数
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <returns>符合条件的文章记录条数</returns>
        public int GetCount(int channelId)
        {
            string sql = "SELECT COUNT(*) FROM dbo.Articles WHERE ChannelId = @ChannelId";
            return (int)_SqlHelper.ExecuteScalar(sql, new SqlParameter("@ChannelId", channelId));
        }
        #endregion

        #region 07.根据栏目Id分页查询文章列表
        /// <summary>
        /// 根据栏目Id分页查询文章列表
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="channelId">栏目Id</param>
        /// <returns>文章列表</returns>
        public List<Article> GetModelList(int start, int end, int channelId)
        {
            List<Article> list = new List<Article>();
            string sql = "SELECT *, ROW_NUMBER() OVER(ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC) AS RowIndex  FROM dbo.Articles WHERE ChannelId = @ChannelId";
            //分页处理
            sql = string.Format("SELECT * FROM ({0}) AS T WHERE T.RowIndex >= @Start AND T.RowIndex <= @End", sql);

            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@ChannelId", channelId),
                new SqlParameter("@Start", start),
                new SqlParameter("@End", end)
            };

            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, args))
            {
                while (reader.Read())
                {
                    list.Add(this.ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 08.根据栏目Id与数量查询文章列表

        /// <summary>
        /// 根据栏目Id与数量查询文章列表
        /// </summary>
        /// <param name="channelId">栏目Id</param>
        /// <param name="count">数量</param>
        /// <returns>文章列表</returns>
        public List<Article> GetModelListByCount(int channelId, int count)
        {
            List<Article> list = new List<Article>();
            string sql = string.Format("SELECT TOP {0} * FROM dbo.Articles WHERE ChannelId = @ChannelId ORDER BY IsTop DESC, Click DESC, Sort DESC, AddTime DESC", count);
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@ChannelId", channelId)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 01.添加方法
        /// <summary>
        /// 添加一个实体对象
        /// </summary>
        /// <param name="article">要添加的实体对象</param>
        /// <returns>添加完毕后的实体对象</returns>
        public Article Add(Article article)
        {
            string sql = "INSERT INTO Articles (ChannelId, UserId, Title, Intitle, SubTitle, Author, Source, Content, ImagePath, ThumbPath, Attachment, Keywords, PrevPage, NextPage, Description, SaveDirectory, FileName, FileExtName, Sort, Publish, IsLink, IsTop, Click, DelFlag, AddTime)  output inserted.Id VALUES (@ChannelId, @UserId, @Title, @Intitle, @SubTitle, @Author, @Source, @Content, @ImagePath, @ThumbPath, @Attachment, @Keywords, @PrevPage, @NextPage, @Description, @SaveDirectory, @FileName, @FileExtName, @Sort, @Publish, @IsLink, @IsTop, @Click, @DelFlag, @AddTime)";
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@ChannelId", ToDBValue(article.ChannelId)),
                new SqlParameter("@UserId", ToDBValue(article.UserId)),
                new SqlParameter("@Title", ToDBValue(article.Title)),
                new SqlParameter("@Intitle", ToDBValue(article.Intitle)),
                new SqlParameter("@SubTitle", ToDBValue(article.SubTitle)),
                new SqlParameter("@Author", ToDBValue(article.Author)),
                new SqlParameter("@Source", ToDBValue(article.Source)),
                new SqlParameter("@Content", ToDBValue(article.Content)),
                new SqlParameter("@ImagePath", ToDBValue(article.ImagePath)),
                new SqlParameter("@ThumbPath", ToDBValue(article.ThumbPath)),
                new SqlParameter("@Attachment", ToDBValue(article.Attachment)),
                new SqlParameter("@Keywords", ToDBValue(article.Keywords)),
                new SqlParameter("@PrevPage", ToDBValue(article.PrevPage)),
                new SqlParameter("@NextPage", ToDBValue(article.NextPage)),
                new SqlParameter("@Description", ToDBValue(article.Description)),
                new SqlParameter("@SaveDirectory", ToDBValue(article.SaveDirectory)),
                new SqlParameter("@FileName", ToDBValue(article.FileName)),
                new SqlParameter("@FileExtName", ToDBValue(article.FileExtName)),
                new SqlParameter("@Sort", ToDBValue(article.Sort)),
                new SqlParameter("@Publish", ToDBValue(article.Publish)),
                new SqlParameter("@IsLink", ToDBValue(article.IsLink)),
                new SqlParameter("@IsTop", ToDBValue(article.IsTop)),
                new SqlParameter("@Click", ToDBValue(article.Click)),
                new SqlParameter("@DelFlag", ToDBValue(article.DelFlag)),
                new SqlParameter("@AddTime", ToDBValue(article.AddTime)),
            };
            //根据newId返回新添加的实体对象
            int newId = (int)_SqlHelper.ExecuteScalar(sql, args);
            return this.GetModel(newId);
        }
        #endregion

        #region 02.删除方法（物理删除）
        /// <summary>
        /// 删除一个实体对象
        /// </summary>
        /// <param name="id">要删除的实体对象Id</param>
        /// <returns>受影响的行数</returns>
        public int PhysicalDelete(int id)
        {
            string sql = "DELETE FROM Articles WHERE Id = @Id";
            SqlParameter arg = new SqlParameter("@Id", id);
            return _SqlHelper.ExecuteNonQuery(sql, arg);
        }
        #endregion

        #region 04.修改方法
        /// <summary>
        /// 修改一个实体对象
        /// </summary>
        /// <param name="article">构造好的要修改的实体对象</param>
        /// <returns>受影响的行数</returns>
        public int Update(Article article)
        {
            StringBuilder sql = new StringBuilder();
            sql.Append("UPDATE Articles SET ChannelId = @ChannelId");
            sql.Append(", UserId = @UserId");
            sql.Append(", Title = @Title");
            sql.Append(", Intitle = @Intitle");
            sql.Append(", SubTitle = @SubTitle");
            sql.Append(", Author = @Author");
            sql.Append(", Source = @Source");
            sql.Append(", Content = @Content");
            sql.Append(", ImagePath = @ImagePath");
            sql.Append(", ThumbPath = @ThumbPath");
            sql.Append(", Attachment = @Attachment");
            sql.Append(", Keywords = @Keywords");
            sql.Append(", PrevPage = @PrevPage");
            sql.Append(", NextPage = @NextPage");
            sql.Append(", Description = @Description");
            sql.Append(", SaveDirectory = @SaveDirectory");
            sql.Append(", FileName = @FileName");
            sql.Append(", FileExtName = @FileExtName");
            sql.Append(", Sort = @Sort");
            sql.Append(", Publish = @Publish");
            sql.Append(", IsLink = @IsLink");
            sql.Append(", IsTop = @IsTop");
            sql.Append(", Click = @Click");
            sql.Append(", DelFlag = @DelFlag");
            sql.Append(", AddTime = @AddTime");
            sql.Append(" WHERE Id = @Id");
            SqlParameter[] args = new SqlParameter[] {
                new SqlParameter("@Id", article.Id)
                ,new SqlParameter("@ChannelId", ToDBValue(article.ChannelId))
                ,new SqlParameter("@UserId", ToDBValue(article.UserId))
                ,new SqlParameter("@Title", ToDBValue(article.Title))
                ,new SqlParameter("@Intitle", ToDBValue(article.Intitle))
                ,new SqlParameter("@SubTitle", ToDBValue(article.SubTitle))
                ,new SqlParameter("@Author", ToDBValue(article.Author))
                ,new SqlParameter("@Source", ToDBValue(article.Source))
                ,new SqlParameter("@Content", ToDBValue(article.Content))
                ,new SqlParameter("@ImagePath", ToDBValue(article.ImagePath))
                ,new SqlParameter("@ThumbPath", ToDBValue(article.ThumbPath))
                ,new SqlParameter("@Attachment", ToDBValue(article.Attachment))
                ,new SqlParameter("@Keywords", ToDBValue(article.Keywords))
                ,new SqlParameter("@PrevPage", ToDBValue(article.PrevPage))
                ,new SqlParameter("@NextPage", ToDBValue(article.NextPage))
                ,new SqlParameter("@Description", ToDBValue(article.Description))
                ,new SqlParameter("@SaveDirectory", ToDBValue(article.SaveDirectory))
                ,new SqlParameter("@FileName", ToDBValue(article.FileName))
                ,new SqlParameter("@FileExtName", ToDBValue(article.FileExtName))
                ,new SqlParameter("@Sort", ToDBValue(article.Sort))
                ,new SqlParameter("@Publish", ToDBValue(article.Publish))
                ,new SqlParameter("@IsLink", ToDBValue(article.IsLink))
                ,new SqlParameter("@IsTop", ToDBValue(article.IsTop))
                ,new SqlParameter("@Click", ToDBValue(article.Click))
                ,new SqlParameter("@DelFlag", ToDBValue(article.DelFlag))
                ,new SqlParameter("@AddTime", ToDBValue(article.AddTime))
            };
            return _SqlHelper.ExecuteNonQuery(sql.ToString(), args);
        }
        #endregion

        #region 05.根据主键获取单个实体对象
        /// <summary>
        /// 根据主键获取单个实体对象
        /// </summary>
        /// <param name="id">主键</param>
        /// <returns>单个实体对象</returns>
        public Article GetModel(int id)
        {
            string sql = "SELECT * FROM Articles WHERE Id = @Id";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@Id", id)))
            {
                if (reader.Read())
                {
                    return ToModel(reader);
                }
                else
                {
                    return null;
                }
            }
        }
        #endregion

        #region 06.获取实体对象集合
        /// <summary>
        /// 获取实体对象集合
        /// </summary>
        /// <returns>实体对象集合</returns>
        public List<Article> GetModelList()
        {
            List<Article> list = new List<Article>();
            string sql = "SELECT * FROM Articles WHERE DelFlag = 0";
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 07.获取总记录条数
        /// <summary>
        /// 返回总记录条数
        /// </summary>
        /// <returns>总记录条数</returns>
        public int GetCount()
        {
            string sql = "SELECT COUNT(*) FROM Articles WHERE DelFlag = 0";
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 10.根据条件获取记录条数（2个参数）
        /// <summary>
        /// 根据条件获取记录条数（2个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string filed2, string arg2)
        {
            string sql = string.Format("SELECT COUNT(*) FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%'", field1, arg1.FilterSql(), filed2, arg2.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 11.根据条件获取记录条数（3个参数）
        /// <summary>
        /// 根据条件获取记录条数（3个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string field2, string arg2, string field3, string arg3)
        {
            string sql = string.Format("SELECT COUNT(*) FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%'", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 12.根据条件获取记录条数（4个参数）
        /// <summary>
        /// 根据条件获取记录条数（4个参数）
        /// </summary>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="field4">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <param name="arg4">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field1, string arg1, string field2, string arg2, string field3, string arg3, string field4, string arg4)
        {
            string sql = string.Format("SELECT COUNT(*) FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%' AND {6} LIKE '%{7}%'", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql(), field4, arg4.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion

        #region 13.根据条件分页获取实体对象集合（1个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（1个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field">字段名称</param>
        /// <param name="arg">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<Article> GetModelList(int start, int end, string field, string arg)
        {
            List<Article> list = new List<Article>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field, arg.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 14.根据条件分页获取实体对象集合（2个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（2个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<Article> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2)
        {
            List<Article> list = new List<Article>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 15.根据条件分页获取实体对象集合（3个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（3个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<Article> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2, string field3, string arg3)
        {
            List<Article> list = new List<Article>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 16.根据条件分页获取实体对象集合（4个参数）
        /// <summary>
        /// 根据条件分页获取实体对象集合（4个参数）
        /// </summary>
        /// <param name="start">起始行</param>
        /// <param name="end">终止行</param>
        /// <param name="field1">字段名称</param>
        /// <param name="field2">字段名称</param>
        /// <param name="field3">字段名称</param>
        /// <param name="field4">字段名称</param>
        /// <param name="arg1">条件参数</param>
        /// <param name="arg2">条件参数</param>
        /// <param name="arg3">条件参数</param>
        /// <param name="arg4">条件参数</param>
        /// <returns>实体对象集合</returns>
        public List<Article> GetModelList(int start, int end, string field1, string arg1, string field2, string arg2, string field3, string arg3, string field4, string arg4)
        {
            List<Article> list = new List<Article>();
            string sql = string.Format("SELECT * FROM (SELECT *, ROW_NUMBER() OVER(ORDER BY Id) AS RowIndex FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%' AND {2} LIKE '%{3}%' AND {4} LIKE '%{5}%' AND {6} LIKE '%{7}%') AS t WHERE RowIndex >= @start AND RowIndex <= @end", field1, arg1.FilterSql(), field2, arg2.FilterSql(), field3, arg3.FilterSql(), field4, arg4.FilterSql());
            using (SqlDataReader reader = _SqlHelper.ExecuteReader(sql, new SqlParameter("@start", start), new SqlParameter("@end", end)))
            {
                while (reader.Read())
                {
                    list.Add(ToModel(reader));
                }
            }
            return list;
        }
        #endregion

        #region 17.根据IDataReader对象返回实体对象方法
        /// <summary>
        /// 根据IDataReader对象返回实体对象方法
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <returns>实体对象</returns>
        public Article ToModel(IDataReader reader)
        {
            Article article = new Article();
            article.Id = (int)ToModelValue(reader as SqlDataReader, "Id");
            article.ChannelId = (int)ToModelValue(reader as SqlDataReader, "ChannelId");
            article.Channel = new ChannelDal().GetModel(article.ChannelId);
            article.UserId = (int)ToModelValue(reader as SqlDataReader, "UserId");
            article.User = UserDal.CreateInstance().GetModel(article.UserId);
            article.Title = (string)ToModelValue(reader as SqlDataReader, "Title");
            article.Intitle = (string)ToModelValue(reader as SqlDataReader, "Intitle");
            article.SubTitle = (string)ToModelValue(reader as SqlDataReader, "SubTitle");
            article.Author = (string)ToModelValue(reader as SqlDataReader, "Author");
            article.Source = (string)ToModelValue(reader as SqlDataReader, "Source");
            article.Content = (string)ToModelValue(reader as SqlDataReader, "Content");
            article.ImagePath = (string)ToModelValue(reader as SqlDataReader, "ImagePath");
            article.ThumbPath = (string)ToModelValue(reader as SqlDataReader, "ThumbPath");
            article.Attachment = (string)ToModelValue(reader as SqlDataReader, "Attachment");
            article.Keywords = (string)ToModelValue(reader as SqlDataReader, "Keywords");
            article.PrevPage = (string)ToModelValue(reader as SqlDataReader, "PrevPage");
            article.NextPage = (string)ToModelValue(reader as SqlDataReader, "NextPage");
            article.Description = (string)ToModelValue(reader as SqlDataReader, "Description");
            article.SaveDirectory = (string)ToModelValue(reader as SqlDataReader, "SaveDirectory");
            article.FileName = (string)ToModelValue(reader as SqlDataReader, "FileName");
            article.FileExtName = (string)ToModelValue(reader as SqlDataReader, "FileExtName");
            article.Sort = (int?)ToModelValue(reader as SqlDataReader, "Sort");
            article.Publish = (bool?)ToModelValue(reader as SqlDataReader, "Publish");
            article.IsLink = (bool?)ToModelValue(reader as SqlDataReader, "IsLink");
            article.IsTop = (bool?)ToModelValue(reader as SqlDataReader, "IsTop");
            article.Click = (int?)ToModelValue(reader as SqlDataReader, "Click");
            article.DelFlag = (bool)ToModelValue(reader as SqlDataReader, "DelFlag");
            article.AddTime = (DateTime)ToModelValue(reader as SqlDataReader, "AddTime");
            return article;
        }
        #endregion

        #region 18.C#值转数据库值空值处理
        /// <summary>
        /// C#值转数据库值空值处理
        /// </summary>
        /// <param name="value">C#值</param>
        /// <returns>处理后的数据库值</returns>
        public object ToDBValue(object value)
        {
            if (value == null)
            {
                return DBNull.Value;
            }
            else
            {
                return value;
            }
        }
        #endregion

        #region 19.数据库值转C#值空值处理
        /// <summary>
        /// 数据库值转C#值空值处理
        /// </summary>
        /// <param name="reader">IDataReader对象</param>
        /// <param name="columnName">列名</param>
        /// <returns>C#值</returns>
        public object ToModelValue(IDataReader reader, string columnName)
        {
            if (reader.IsDBNull(reader.GetOrdinal(columnName)))
            {
                return null;
            }
            else
            {
                return reader[columnName];
            }
        }
        #endregion

        #region 09.根据条件获取记录条数（1个参数）
        /// <summary>
        /// 根据条件获取记录条数（1个参数）
        /// </summary>
        /// <param name="field">字段名称</param>
        /// <param name="arg">条件参数</param>
        /// <returns>记录条数</returns>
        public int GetCount(string field, string arg)
        {
            string sql = string.Format("SELECT COUNT(*) FROM Articles WHERE DelFlag = 0 AND {0} LIKE '%{1}%'", field, arg.FilterSql());
            return (int)_SqlHelper.ExecuteScalar(sql);
        }
        #endregion
    }
}
